package com.weixing.mall.provider.service.impl;


import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.weixing.mall.base.constant.GlobalConstant;
import com.weixing.mall.core.enums.OrderSysTagEnum;
import com.weixing.mall.provider.api.model.enums.AgentLevelEnum;
import com.weixing.mall.provider.api.model.enums.OrderTypeEnum;
import com.weixing.mall.provider.mapper.DayReportCustomerMapper;
import com.weixing.mall.provider.model.domain.Order;
import com.weixing.mall.provider.model.enums.*;
import com.weixing.mall.provider.model.vo.report.customer.*;
import com.weixing.mall.provider.service.IDayReportCustomerService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * @author jzy
 * @date 2020/6/12
 */
@Service
public class DayReportCustomerServiceImpl extends ServiceImpl<DayReportCustomerMapper, Order> implements IDayReportCustomerService {

    @Override
    public IPage<CustomerReportFinanceVo> customerFinance(Integer roleType, Long agentId,Long agentPid, Integer pRole,String startTime, String endTime, Integer orderType, Long actId, String actName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            select=select+",0 as earnProfit";
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            select= select+",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit"+"";
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+",1,0) as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.belong_type="+GlobalConstant.PROFIT_BELONG_TYPE_OWN+",true,null)) as ownQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualTurnover" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+",o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //排序
        q.orderByDesc("isOwn");
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        IPage<CustomerReportFinanceVo> pageData = baseMapper.customerFinance(page, q);
        if (pageData.getRecords().size()>0){
            //合计的数据放在数组第一个
            pageData.getRecords().add(0,customerFinanceSum(roleType,agentId,agentPid,pRole,startTime,endTime,orderType,actId,actName));
        }
        return pageData;
    }

    public CustomerReportFinanceVo customerFinanceSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, Integer orderType, Long actId, String actName) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'as userRole";
            //如果角色类型为空，角色类型为客服的订单
            q.eq("agent.role_type",1);
        }else{
            if (roleType==1){
                select="agent.agent_id agentId,agent.role_type roleType" +
                        ",'合计' userRole" +
                        ",0 as earnProfit" +
                        "";
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
            }
            if (roleType==2){
                select="agent.agent_id,agent.role_type roleType" +
                        ",'合计' userRole" +
                        ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit" +
                        "";
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
            }
        }
        q.select(select+
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualTurnover" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+",o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);

        return baseMapper.customerFinanceSum(q);
    }
    @Override
    public IPage<CustomerReportFinanceVo> customerFinancePerson(Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, Integer orderType, Long actId,String actName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,2 as roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if(roleType!=null&&roleType==1){
            select=select+",0 as earnProfit";
        }
        if (roleType!=null&&roleType==2){
            select=select+
                    ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit" +
                    "";
        }
        q.eq("agent.agent_id",agentId);
        q.eq(agentPid!=null,"agent.agent_pid",agentPid);
        q.eq("agent.belong_type",1);
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(roleType!=null,"agent.role_type",roleType);
        q.select(select+
                ",1 as isOwn" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualTurnover" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+",o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        IPage<CustomerReportFinanceVo> pageData = baseMapper.customerFinance(page, q);
        return pageData;
    }

    @Override
    public IPage<CustomerReportDetailVo> customerFinanceDetail(Integer isOwn,Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, String actName,String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName,o.agent_id agentId,o.agent_name agentName,o.sys_tag,if(agent.role_type=1,childAgent.agent_discount,childAgent2.agent_discount) childDiscount" +
                ",o.distributor_id distributorId,o.distributor_name distributorName,o.act_name" +
                ",o.order_status,o.buyer,o.order_sn,o.out_trade_no,o.order_type,agent.agent_discount discount,agent.agent_sn,o.shipper_name,log.operator_name confirmName,o.created_time,o.audit_time" +
                ",goods.goods_id,goods.product_id,goods.goods_img,goods.goods_name,goods.goods_sn,goods.commodity_code" +
                ",goods.style_no,goods.cat_id,goods.cat_name,goods.color,goods.size,goods.partition_name,goods.sku_desp,goods.price,goods.market_price" +
                ",goods.cost_price,goods.height,goods.weight,goods.quantity,goods.product_status" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone" +
                ",afterSale.after_sale_type,afterSale.after_sale_result,afterSale.status afterSaleStatus,afterSale.customer_remark,max(log.created_time) auditedTime" +
                ",if("+roleType+"=2,agent.agent_discount,1) auditorDiscount" +
                //",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount) discountPrice" +
                ",if(o.order_type=3,o.order_price,if(agent.role_type=2,o.order_price*agent.agent_discount,agent.need_receive)) as discountPrice" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",if( agent.receivor_role=2,agent.need_uppay,0),'') refundHigherAmount" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",agent.need_receive,'') refundLowerAmount" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",agent.payer,'') refundObjectName" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",agent.payer_id,'') refundObjectId" +
/*
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.receivor_role=2,0,agent.need_uppay) refundHigherAmount" +
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",0,agent.need_receive) refundLowerAmount" +
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",null,agent.payer) refundObjectName" +
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",null,agent.payer_id) refundObjectId" +
*/
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        q.groupBy("o.id");
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }else if (orderStatus.equals(8)){// 8取消和断货的总量
                q.and(i->i.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue()).or().eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue()));
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        //订单操作时间
        q.isNotNull("log.created_time");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerReportDetailVo> dayReportDetail = baseMapper.getDayReportDetail(initMpPage, q);
        return dayReportDetail;
    }

    @Override
    public Map<String, Object> customerFinanceTotal(Integer isOwn,Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId,String actName) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("sum(o.order_price) as saleAmount" +
                ",sum(if(o.order_type=3,o.order_price,if(agent.role_type=2,o.order_price*agent.agent_discount,agent.need_receive))) as actualTurnover" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
               //没这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        //订单操作时间
        q.isNotNull("log.created_time");
        Map<String, Object> map = baseMapper.getOrderMap(q);
        return map;
    }

    @Override
    public IPage<CustomerReportDiscountVo> customerDiscount(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, BigDecimal discount, Integer groupType, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            /*select=select+
                    ",(select count(0) from ads_media_orderd_record record left join amc_agent a1 on a1.id=record.agent_id where a1.customer_id=agent.agent_id and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where a1.customer_id=agent.agent_id and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";*/
        }else{
            //根据收款人的角色来筛选，以代理商分组
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
//                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
                    .or(z->z.eq("agent.role_type",2)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            /*select=select+
                    ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where ( record.agent_id="+agentId+" or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where (record.agent_id="+agentId+"  or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";*/
        }
        if (groupType!=null){
            q.groupBy("agent.agent_id","agent.role_type","agent.agent_discount");
        }else{
            return initMpPage;
        }
        q.select(select+
                ",1 as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣方式
        q.eq(discount!=null,"agent.agent_discount",discount);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        q.eq("o.order_type",OrderTypeEnum.NORMAL.getValue());
        //IPage<CustomerReportDiscountVo> pageData = baseMapper.customerDiscount(initMpPage, q);
        IPage<CustomerReportDiscountVo> pageData = baseMapper.customerDiscount2(initMpPage, q,roleType,startTime,endTime,agentId,agentPid);

        if(pageData.getRecords().size()>0){
            pageData.getRecords().add(0,customerDiscountSum(roleType,agentId,agentPid,pRole,startTime,endTime,discount,groupType));
        }
        return pageData;
    }

    @Override
    public List<BigDecimal> discountList() {
        return baseMapper.discountList();
    }


    public CustomerReportDiscountVo customerDiscountSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, BigDecimal discount, Integer groupType) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' as customerName";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            select=select+",0 as isOwn"+
                    //2021年1月11日
                    ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";
        }else{
            select=select+",agent.agent_id agentId,"+agentId+" adsAgentId,agent.role_type roleType";
            if (roleType==1){
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
                select=select+",2 as isOwn"+
                        //2021年1月11日
                        ",(select count(0) from ads_media_orderd_record record left join amc_agent a1 on a1.id=record.agent_id where a1.customer_id="+agentId+" and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                        ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where a1.customer_id="+agentId+" and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                        "";
            }
            if (roleType==2){
                //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
                select=select+",0 as isOwn"+
                        //2021年1月11日
                        ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where ( record.agent_id="+agentId+" or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                        ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where (record.agent_id="+agentId+"  or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                        "";
            }
        }
        q.select(select+
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣方式
        q.eq(discount!=null,"agent.agent_discount",discount);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.NORMAL.getValue());
        return baseMapper.customerDiscountSum(q);
    }
    @Override
    public IPage<CustomerReportDiscountPersonVo> customerDiscountPerson(Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, BigDecimal discount,Page initMpPage) {
        //添加过订单记录后修改
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            q.eq("agent.role_type",roleType);
            q.eq("agent.agent_id",agentId);
            q.eq(agentPid!=null,"agent.agent_pid",agentPid);
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price*agent.agent_discount,-o.order_price*agent.agent_discount),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price*agent.agent_discount,-o.order_price*agent.agent_discount)) as actualAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price*agent.agent_discount,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price*agent.agent_discount,0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price*agent.agent_discount,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price*agent.agent_discount,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price*agent.agent_discount,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price*agent.agent_discount,0)) as actualCancelAmountTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣
        q.eq(discount!=null,"agent.agent_discount",discount);
        //只筛选正式的数据
        q.eq("o.order_type",OrderTypeEnum.NORMAL.getValue());
        //订单操作时间

        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        IPage<CustomerReportDiscountPersonVo> pageData = baseMapper.customerDiscountPerson(initMpPage, q);
        return pageData;
    }

    @Override
    public IPage<CustomerAdvertisementDetailVo> customerDiscountAds(Integer isOwn,Integer roleType, Long agentId,String plateformName
            , String chatNum, String directorName, String orderedAgent
            , String payTimeStart, String payTimeEnd, String launchDateStart, String launchDateEnd, String endOrderDateStart, String endOrderDateEnd
            ,String sortField, String sortType,Page initPage) {
        // TODO REPORT 性价比字段不知道怎样计算
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("ads.id,mediaType.media_name,platform.plateform_name,platform.chat_num,ads.page_site pageSite,ads.launch_date,ads.end_order_date,ads.price" +
                ",agent.agent_name orderedAgent,ads.staus,ads.article_title,ads.read_amount,ads.fans,sysUser.real_name directorName,record.pay_time");
        q.ge(StrUtil.isNotEmpty(payTimeStart),"date_format(record.pay_time,'%Y-%m-%d')",payTimeStart);
        q.le(StrUtil.isNotEmpty(payTimeEnd),"date_format(record.pay_time,'%Y-%m-%d')",payTimeEnd);
        q.ge(StrUtil.isNotEmpty(launchDateStart),"date_format(ads.launch_date,'%Y-%m-%d')",launchDateStart);
        q.le(StrUtil.isNotEmpty(launchDateEnd),"date_format(ads.launch_date,'%Y-%m-%d')",launchDateEnd);
        q.ge(StrUtil.isNotEmpty(endOrderDateStart),"date_format(ads.end_order_date,'%Y-%m-%d')",endOrderDateStart);
        q.le(StrUtil.isNotEmpty(endOrderDateEnd),"date_format(ads.end_order_date,'%Y-%m-%d')",endOrderDateEnd);
        q.like(StrUtil.isNotEmpty(plateformName),"platform.plateform_name",plateformName);
        q.like(StrUtil.isNotEmpty(chatNum),"platform.chat_num",chatNum);
        q.like(StrUtil.isNotEmpty(directorName),"sysUser.real_name",directorName);
        q.like(StrUtil.isNotEmpty(orderedAgent),"agent.agent_name",orderedAgent);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        if (roleType!=null){
            if(roleType==2){
                q.eq(agentId!=null,"record.agent_id",agentId);
            }
            if(roleType==1){
                q.eq(agentId!=null,"agent.customer_id",agentId);
            }
        }
        IPage<CustomerAdvertisementDetailVo> page = baseMapper.customerDiscountAds(initPage, q);
        return page;
    }

    @Override
    public IPage<CustomerSeckillDayReportVo> customerSeckill(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, Long actId, String actName,String sortField, String sortType, Page initMpPage) {
        //添加过订单操作记录修改
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarket" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmountTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price),0)) as actualAmountMarket" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                ",sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebateSelf" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                ",(sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0))-sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0))) lowerRebate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        IPage<CustomerSeckillDayReportVo> pageData = baseMapper.customerSeckill(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().forEach(i->{
                i.setRebateTeam(baseMapper.sumTeamRebate(startTime,endTime,i.getRoleType(),i.getAgentId()));
            });
            pageData.getRecords().add(0,customerSeckillSum(roleType,agentId,agentPid,pRole,startTime,endTime,actId));
        }
        return pageData;
    }

    /**
     * 计算秒杀活动团队返还款
     * @param startTime
     * @param endTime
     * @param roleType
     * @param agentId
     * @return
     */
    public BigDecimal sumTeamRebate(String startTime,String endTime,Integer roleType,Long agentId){
        return baseMapper.sumTeamRebate(startTime,endTime,roleType,agentId);
    }

    /**
     * 秒杀财务日报合计
     * @param roleType
     * @param agentId
     * @param startTime
     * @param endTime
     * @param actId
     * @return
     */
    public CustomerSeckillDayReportVo customerSeckillSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, Long actId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'as userRole";
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            select="'合计' userRole";
            if (roleType==1){
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
            }
            if (roleType==2){
                //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
            }
        }
        q.select(select+
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarket" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,o.order_price,agent.need_receive),0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,o.order_price,agent.need_receive),0)) as actualCancelAmountTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price),0)) as actualAmountMarket" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                ",sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebateSelf" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq(actId!=null,"o.act_id",actId);
        CustomerSeckillDayReportVo customerSeckillDayReportVo = baseMapper.customerSeckillSum(q);
        customerSeckillDayReportVo.setRebateTeam(baseMapper.sumTeamRebate(startTime,endTime,roleType,agentId));
        return customerSeckillDayReportVo;
    }
    @Override
    public IPage<CustomerUserReportVo> customerUserReport(String contactUser, String contactPhone, Integer isRepeat, String startTime, String endTime, Integer filter, Integer buyNumMin, Integer buyNumMax, Integer buyTimes, String orderType, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("address.contact_user  contactUser,address.contact_phone contactPhone,goods.height,goods.weight,o.buyer_id buyerId,o.order_from orderFrom" +
                ",IFNULL(sum(goods.quantity),0) buyNum" +
                ",if(count(0)>1,1,0) isRepeat" +
                ",count(0) buyTimes" +
                ",DATE_FORMAT(min(o.created_time),'%Y-%m-%d %H:%i:%s') joinTime" +
                "");
        q.like(StrUtil.isNotEmpty(contactUser),"address.contact_user",contactUser);
        q.like(StrUtil.isNotEmpty(contactPhone),"address.contact_phone",contactPhone);
        q.having(isRepeat!=null,"isRepeat="+isRepeat);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.created_time,'%Y-%m-%d')",endTime);
        q.having(buyNumMin!=null,"buyNumMin>="+buyNumMin);
        q.having(buyNumMax!=null,"buyNumMax<="+buyNumMax);
        if (buyTimes!=null){
            if (buyTimes==0){
                q.having("buyTimes=0");
            }
            if (buyTimes==1){
                q.having("buyTimes>0");
            }
        }
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.groupBy("o.buyer_id","o.order_from");
        return baseMapper.customerUserReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerUserReportVo> customerUserReport2(String contactUser, String contactPhone, String startTime, String endTime, Integer buyNumMin, Integer buyNumMax, String orderType, String sortField, String sortType,Integer isRepeat2,Integer isHave, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("address.contact_user  contactUser,address.contact_phone contactPhone,goods.height,goods.weight,o.buyer_id buyerId,o.order_from orderFrom" +
                ",IFNULL(sum(goods.quantity),0) buyNum" +
                ",if(count(0)>1,1,0) isRepeat" +
                ",IF((SELECT count(0) FROM (SELECT es.id FROM omc_order_express es WHERE es.contact_phone = address.contact_phone GROUP BY LEFT(es.created_time,10)) total) > 1, 1, 0 ) isRepeat2" +
                ",count(0) buyTimes" +
                ",TIMESTAMPDIFF(DAY,max( address.created_time ),now()) time10" +
                ",(SELECT DATE_FORMAT( min( es.created_time ), '%Y-%m-%d %H:%i:%s' ) FROM omc_order_express es WHERE es.contact_phone = address.contact_phone ) joinTime" +
                "");
        q.like(StrUtil.isNotEmpty(contactUser),"address.contact_user",contactUser);
        q.like(StrUtil.isNotEmpty(contactPhone),"address.contact_phone",contactPhone);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(address.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(address.created_time,'%Y-%m-%d')",endTime);
        q.having(buyNumMin!=null,"buyNum>="+buyNumMin);
        q.having(buyNumMax!=null,"buyNum<="+buyNumMax);
        //q.having(isRepeat2!=null,"isRepeat2="+isRepeat2);
        q.having(isRepeat2!=null,"IF((SELECT count(0) FROM (SELECT es.id FROM omc_order_express es WHERE es.contact_phone = address.contact_phone GROUP BY LEFT(es.created_time,10)) total) > 1, 1, 0 )="+isRepeat2);
        if(isHave != null && isHave == 1){//十日内有购买记录
            q.having("time10<=10");
        }else if(isHave != null && isHave == 0){//十日内无购买记录
            q.having("time10>10");
        }
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.isNotNull("address.contact_phone");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        /*q.groupBy("o.buyer_id","o.order_from");*/
        q.groupBy("address.contact_phone");//库户要求按订单地址手机号区分客户
        return baseMapper.customerUserReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerUserReportBuyLogVo> customerUserReportBuyLog(Long buyerId, Integer orderFrom, String goodsName, String customerName, String orderSn, String agentName, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.buyer_id buyerId,o.buyer,o.agent_name agentName,o.customer_name customerName," +
                "o.order_from orderFrom,o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_sn,goods.goods_name goodsName,goods.sku_desp skuDesp,goods.color,goods.size," +
                "address.contact_user contactUser,address.contact_phone contactPhone,DATE_FORMAT(o.created_time,'%Y-%m-%d %H:%i:%s') orderCreateTime," +
                "CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address," +
                "o.audit_time auditTime,o.order_status orderStatus");
        q.like(StrUtil.isNotEmpty(goodsName),"goods.goods_name",goodsName);
        q.like(StrUtil.isNotEmpty(customerName),"o.customer_name",customerName);
        q.like(StrUtil.isNotEmpty(agentName),"o.agent_name",agentName);
        q.like(StrUtil.isNotEmpty(orderSn),"o.order_sn",orderSn);
        q.eq(buyerId!=null,"o.buyer_id",buyerId);
        q.eq(orderFrom!=null,"o.order_from",orderFrom);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.created_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerUserReportBuyLog(initMpPage, q);
    }

    @Override
    public IPage<CustomerUserReportBuyLogVo> customerUserReportBuyLog2(Long buyerId,String contactPhone, Integer orderFrom, String goodsName, String customerName, String orderSn, String agentName, String startTime, String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.buyer_id buyerId,o.buyer,o.agent_name agentName,o.customer_name customerName," +
                "o.order_from orderFrom,o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_sn,goods.goods_name goodsName,goods.sku_desp skuDesp,goods.color,goods.size," +
                "address.contact_user contactUser,address.contact_phone contactPhone,DATE_FORMAT(o.created_time,'%Y-%m-%d %H:%i:%s') orderCreateTime," +
                "CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address," +
                "o.audit_time auditTime,o.order_status orderStatus");
        q.like(StrUtil.isNotEmpty(goodsName),"goods.goods_name",goodsName);
        q.like(StrUtil.isNotEmpty(customerName),"o.customer_name",customerName);
        q.like(StrUtil.isNotEmpty(agentName),"o.agent_name",agentName);
        q.like(StrUtil.isNotEmpty(orderSn),"o.order_sn",orderSn);
        q.eq(buyerId!=null,"o.buyer_id",buyerId);
        q.eq(contactPhone!=null,"address.contact_phone",contactPhone);
        q.eq(orderFrom!=null,"o.order_from",orderFrom);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.created_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerUserReportBuyLog(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillReportVo> customerSeckillReport(Integer role, String buyer, Integer orderType, Long actId, String startTime, String endTime,String actName,String mobile, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.id,o.order_sn,goods.goods_id,goods.goods_img goodsImg,goods.goods_sn goodsSn,goods.cat_name catName,goods.commodity_code commodityCode,o.order_type,o.act_name actName,goods.goods_name goodsName,goods.price" +
                ",act.discount,o.order_price discountPrice,goods.sku_desp skuDesp,goods.color,goods.size,o.buyer,o.buyer_id buyerId" +
                ",address.contact_user contactUser,address.contact_phone contactPhone,o.audit_time" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.customer_id customerId,o.customer_name customerName,o.agent_id agentId,o.agent_name agentName,o.distributor_id distributorId,o.distributor_name distributorName" +
                ",o.sys_tag orderStatus" +
                "");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");

        q.like(StrUtil.isNotEmpty(buyer),"o.buyer",buyer);
        q.eq(role!=null,"o.order_from",role);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerSeckillReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerReportRegularVo> customerReportRegular(String agentName, String startTime, String endTime, String sortField, String sortType,Integer orderType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.agent_id,agent.agent_name" +
                ",count(0) quantity" +
                ",sum(o.order_price) amount" +
                ",date_format(log.created_time,'%Y-%m-%d') auditTime");
        q.like(StrUtil.isNotEmpty(agentName),"agent.agent_name",agentName);
        q.eq(null != orderType,"o.order_type",orderType);
        // 客服审核通过才显示
        q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
        q.eq("agent.agent_level",AgentLevelEnum.DEALER.getValue());
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间倒序
        q.orderByDesc(StrUtil.isEmpty(sortField),"quantity");
        q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id");
        IPage<CustomerReportRegularVo> page = baseMapper.customerReportRegular(initMpPage, q);
        if (page.getRecords().size()>0){
            //合计的数据放在数组第一个
            page.getRecords().add(0,customerReportRegularSum(agentName,startTime,endTime,orderType));
        }
        return page;
    }
    public CustomerReportRegularVo customerReportRegularSum(String agentName, String startTime, String endTime,Integer orderType) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("'合计' as agentName" +
                ",count(0) quantity" +
                ",sum(o.order_price) amount" +
                "");
        q.like(StrUtil.isNotEmpty(agentName),"agent.agent_name",agentName);
        q.eq(null != orderType,"o.order_type",orderType);
        // 客服审核通过才显示
        q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
        q.eq("agent.agent_level",AgentLevelEnum.DEALER.getValue());
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerReportRegularSum(q);
    }

    @Override
    public IPage<CustomerRefundReportVo> customerRefundReport(Integer roleType, Long agentId,Long agentPid,Integer pRole, String userName, Long actId, Integer orderType, String startTime, String endTime,String monthTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="date_format(log.created_time,'%Y-%m-%d') dayTime,o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type","o.order_type");
        }else{
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type","o.order_type");
        }
        q.select(select +",o.order_type" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+",1,0) as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0))as cancelAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" and afterSale.after_sale_result="+AfterSaleResultEnum.IN_DEFE.getValue()+",true,null)) as flawQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" and afterSale.after_sale_result="+AfterSaleResultEnum.PROBLEM_ORDER.getValue()+",true,null)) as issueQuantity" +
                ",IFNULL(sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" and afterSale.after_sale_result="+AfterSaleResultEnum.PROBLEM_ORDER.getValue()+",o.order_price,0)),0) as issueAmount" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.likeRight(StrUtil.isNotEmpty(monthTime),"date_format(log.created_time, '%Y-%m-%d')",monthTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //排序
        q.orderByDesc("dayTime");
        q.orderByDesc("isOwn");
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        return baseMapper.customerRefundReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerBuyerReportVo> customerBuyerReport(Integer roleType, Long agentId, String buyer, Integer orderType, Integer role, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.buyer,goods.goods_img,o.order_sn,o.out_trade_no,o.order_status,o.is_cliam,o.order_price,address.contact_user,address.contact_phone,goods.goods_sn,goods.sku_desp,goods.color,goods.size" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                "");
        q.eq(roleType!=null,"agent.role_type",roleType);
        q.eq(agentId!=null,"agent.agent_id",agentId);
        q.eq(role!=null,"o.order_from",role);
        q.eq(orderType!=null,"o.order_type",orderType);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.like(StrUtil.isNotEmpty(buyer),"o.buyer",buyer);
        q.groupBy("o.id");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.customerBuyerReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillRebateVo> customerSeckillRebate(Integer roleType, Long agentId, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String  select="agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type as roleType" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            // 如果是角色类型是客服，筛选客服名下代理商的数据
            q.eq(roleType==1,"agent.role_type",2).eq("agent.kefu_id",agentId);
            //如果是角色类型是代理商，筛选代理商名下代理商的数据
            q.eq(roleType==2,"agent.agent_pid",agentId).eq("agent.role_type",2);
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",sum(if(agent.belong_type=1 and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebateTeam" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL1+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusOne" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL2+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusTwo" +
                ",sum(if(agent.belong_type=1  and (agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sum" +
                ",sum(if((agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sumTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerSeckillRebateVo> pageData = baseMapper.customerSeckillRebate(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().add(0,customerSeckillRebateSum(roleType,agentId,userName,startTime,endTime));
        }
        return pageData;
    }

    public CustomerSeckillRebateVo customerSeckillRebateSum(Integer roleType, Long agentId, String userName, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' userRole";
        if (agentId!=null){
            select=select+",'"+agentId+"' agentId";
        }
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            // 如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2))
                    .or(z->z.eq("agent.agent_pid",agentId)).eq("agent.role_type",2));
        }
        q.select(select+
                ",sum(if(agent.belong_type=1  and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebateTeam" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL1+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusOne" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL2+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusTwo" +
                ",sum(if(agent.belong_type=1  and (agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sum" +
                ",sum(if((agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sumTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        return baseMapper.customerSeckillRebateSum(q);
    }

    @Override
    public IPage<CustomerSeckillRebateAllVo> customerSeckillRebateAll(Integer roleType, Long agentId, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            // 如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2))
                    .or(z->z.eq("agent.agent_pid",agentId)).eq("agent.role_type",2));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",sum(if(agent.belong_type=1 and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebateTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerSeckillRebateAllVo> pageData = baseMapper.customerSeckillRebateAll(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().add(0,customerSeckillRebateAllSum(roleType,agentId,userName,startTime,endTime));
        }
        return pageData;
    }

    public CustomerSeckillRebateAllVo customerSeckillRebateAllSum(Integer roleType, Long agentId, String userName, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' userRole";
        if (agentId!=null){
            select=select+",'"+agentId+"' agentId";
        }
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            // 如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2))
                    .or(z->z.eq("agent.agent_pid",agentId)).eq("agent.role_type",2));
        }
        q.select(select+
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.belong_type=1 and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebateTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        return baseMapper.customerSeckillRebateAllSum( q);
    }

    @Override
    public IPage<CustomerSeckillRebateDetailVo> customerSeckillRebateDetail(Integer type, Long agentId, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("goods.product_id,goods.goods_id,goods.goods_img,goods.goods_name,goods.sku_desp,goods.color,goods.size,goods.quantity" +
                ",goods.goods_sn,goods.commodity_code,goods.style_no,goods.cat_id,goods.cat_name,goods.market_price,goods.price,goods.cost_price" +
                ",goods.product_status,goods.height,goods.weight,o.out_trade_no,agent.agent_profit amount,agent.profit_ratio ratio,o.id,o.order_sn" +
                ",o.order_price,o.audit_time,o.audit_status,o.buyer,o.agent_name,o.agent_sn,o.discount,o.agent_cliam_line,o.distributor_name,o.customer_name" +
                ",o.order_from,o.order_status" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone");
        //个人返还款
        if (type.equals(1)){
            q.eq("agent.agent_id",agentId);
            q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_REBATE);
            q.eq("agent.belong_type",1);
        }
        //团队返还款
        if (type.equals(2)){
            q.eq("agent.agent_id",agentId);
            q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_REBATE);
        }
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerSeckillRebateDetailVo> pageData = baseMapper.customerSeckillRebateDetail(initMpPage, q);
        return pageData;
    }

    @Override
    public IPage<CustomerSeckillBonusOneVo> customerSeckillBonusOne(Long agentId, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId1,agent.agent_name agentName1" +
                ",team.agent_id agentId,team.agent_name agentName,agent.agent_profit bonusOne ");
        q.eq("agent.agent_id",agentId);
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL1);
        q.eq("agent.order_type",OrderTypeEnum.SECKILL.getValue());
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.customerSeckillBonusOne(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillBonusTwoVo> customerSeckillBonusTwo(Long agentId, String userName1, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId2,agent.agent_name agentName2" +
                ",team.obligee_id1st agentId1,team.obligee1st agentName1,team.agent_id agentId,team.agent_name agentName,agent.agent_profit bonusOne ");
        q.eq("agent.agent_id",agentId);
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL2);
        q.eq("agent.order_type",OrderTypeEnum.SECKILL.getValue());
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.customerSeckillBonusTwo(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillBonusDetailVo> customerSeckillBonusDetail(Integer type, Long agentId, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("goods.product_id,goods.goods_id,goods.goods_img,goods.goods_name,goods.sku_desp,goods.color,goods.size,goods.quantity" +
                ",goods.goods_sn,goods.commodity_code,goods.style_no,goods.cat_id,goods.cat_name,goods.market_price,goods.price,goods.cost_price" +
                ",goods.product_status,goods.height,goods.weight,o.out_trade_no,agent.agent_profit amount,agent.profit_ratio ratio,o.id,o.order_sn" +
                ",o.order_price,o.audit_time,o.audit_status,o.buyer,o.agent_name,o.agent_sn,o.discount,o.agent_cliam_line,o.distributor_name,o.customer_name" +
                ",o.order_from,o.order_status" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone");
        //一级分红
        if (type.equals(1)){
            q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL1);
        }
        //二级分红
        if (type.equals(2)){
            q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL2);
        }
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.agent_id",agentId);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return  baseMapper.customerSeckillBonusDetail(initMpPage, q);
    }

    @Override
    public IPage<CustomerReportAgentRankingVo> customerReportAgentRanking(Integer orderType, Long actId, Integer isTeam, String startTime, String endTime, String sortField, String sortType,String agentName, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.act_id,o.act_name,o.order_type,o.customer_id,o.customer_name,agent.agent_id,agent.agent_name userName,2 as roleType" +
                ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        //个人数据
        q.eq(isTeam!=null&&isTeam==1,"agent.belong_type",1);
        q.ne("agent.agent_level",AgentLevelEnum.KEFU.getValue());
        q.select(select+
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) quantity"+
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) amount"+
                ",(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))) actualQuantity"+
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))-sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0))-sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0))-sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0))) actualAmount"
                );
        q.eq(actId!=null,"o.act_id",actId);
        q.eq(orderType!=null,"o.order_type",orderType);
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        // 排除分红订单只统计个人和团队订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //排序
        if(StrUtil.isEmpty(sortField)){
          q.orderByDesc("quantity");
        }else{
            q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        }
        q.groupBy("agent.agent_id","o.order_type","o.act_id");
        return baseMapper.customerReportAgentRanking(initMpPage, q);
    }
}
